﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using MySql;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Data;
using System.IO;

namespace BaseHelper.Data
{
    public class MySqlDBHelper
    {

        public string Error;
        public int PageSize = 30;
        public string _connstr;
        public string _lastSql;
        private DBInfo _dbInfo;
        private MySqlConnection _conn;
        private object _locker = new object();
        private bool _isKeepLive = false;

        public string GetLastSql
        {
            get { return _lastSql; }
        }

        public bool IsConntected
        {
            get
            {
                if (_dbInfo.KeepLive || _isKeepLive)
                {
                    return !(_conn==null || _conn.State == System.Data.ConnectionState.Closed || _conn.State == System.Data.ConnectionState.Broken);
                    //return _conn != null && (_conn.State == System.Data.ConnectionState.Open || _conn.State == System.Data.ConnectionState.Executing || _conn.State == System.Data.ConnectionState.Fetching);
                }
                return false;
            }
        }

        public MySqlDBHelper(DBInfo db, bool isKeepLive=false)
        {
            _isKeepLive = isKeepLive;
            _dbInfo = db;
            _connstr = "server=" + db.IP + ";port=" + db.Port + ";database=" + db.DBName + ";user=" + db.User + ";password=" + db.Pass + ";Convert Zero Datetime=True;Allow Zero Datetime=True;pooling=true;min pool size=1;max pool size=1024;Charset=" + db.CharSet;
           
        }

        ~MySqlDBHelper()
        {
            this.Dispose();
            GC.SuppressFinalize(this);
        }

        public bool Open(DBInfo db) 
        {
            Error = "";
            _dbInfo = db;
            _connstr = "server=" + db.IP + ";port=" + db.Port + ";database=" + db.DBName + ";user=" + db.User + ";password=" + db.Pass + ";Convert Zero Datetime=True;Allow Zero Datetime=True;pooling=true;min pool size=1;max pool size=1024;Charset=" + db.CharSet; ;


            try
            {
                using (MySqlConnection conn = new MySqlConnection(_connstr))
                {
                    conn.Open();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Error = ex.Message;

            
                return false;
            }
        }

        private MySqlConnection GetSqlConnection()
        {
            if (_dbInfo.KeepLive || _isKeepLive)
            {
                lock (_locker)
                {
                    if (_conn == null)
                    {
                        _conn = new MySqlConnection(_connstr);
                    }

                    if (!this.IsConntected)
                    {
                        try
                        {
                            _conn.Open();
                            return _conn;
                        }
                        catch (Exception ex)
                        {
                            Error = "MySqlHelper无法连接数据库," + ex.Message;
                            throw ex;
                        }

                    }
                    else
                        return _conn;
                }
            }
            else
            {
                MySqlConnection conn = new MySqlConnection(_connstr);
                try
                {

                    conn.Open();
                    return conn;
                }
                catch (Exception ex)
                {
                    Error = "MsSqlHelper无法连接数据库," + ex.Message;
                    throw ex;
                }

            }
        }

        public Hashtable[] ExecuteHashtable(string sql) 
        {
            Hashtable[] hts=null;

            try
            {
                _lastSql = sql;
                Error = "";
                MySqlConnection conn=GetSqlConnection();

                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (_dbInfo.KeepLive)
                    {
                        lock (_locker)
                        {
                            MySqlDataReader dr = cmd.ExecuteReader();


                            if (dr.HasRows)
                                hts = DataReaderToHashtable(dr);
                            dr.Close();
                        }
                    }
                    else
                    {
                        MySqlDataReader dr = cmd.ExecuteReader();

                        if (dr.HasRows)
                            hts = DataReaderToHashtable(dr);
                        dr.Close();

                        conn.Close();

                    }

                }

            }
            catch (Exception ex)
            {
                Error = ex.Message;
                LogHelper.Err("RTSDK.RTModeDB.Helper.MySqlHelper.cs", "ExecuteHashtable() ：" + sql, ex);
            }
            finally
            {
                if (_dbInfo.IsDebugSql)
                    Console.WriteLine(sql);
            }
            return hts;
        }

        public bool IsDataReaderHasRow(string sql)
        {
            bool hasRow = false;
            _lastSql = sql;
            Error = "";

            try
            {
                MySqlConnection conn=GetSqlConnection();

                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (_dbInfo.KeepLive)
                    {
                        lock (_locker)
                        {
                            MySqlDataReader dr = cmd.ExecuteReader();
                            hasRow = dr.HasRows;
                            dr.Close();
                        }
                    }
                    else
                    {
                        MySqlDataReader dr = cmd.ExecuteReader();
                        hasRow = dr.HasRows;
                        dr.Close();
                        conn.Close();
                    }
                }
                
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                LogHelper.Err("RTSDK.RTModeDB.Helper.MySqlHelper.cs", "ExecuteHashtable()", ex);
            }
            finally
            {
                if (_dbInfo.IsDebugSql)
                    Console.WriteLine(sql);
            }
            return hasRow;
        }

        public int NonQuery(string sql, MySqlParameter[] sqlParms = null)
        {
            _lastSql = sql;
            Error = "";

            try
            {
                MySqlConnection conn=GetSqlConnection();

                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (_dbInfo.KeepLive)
                    {
                        lock (_locker)
                        {
                            if (sqlParms != null)
                                cmd.Parameters.AddRange(sqlParms);
                            int ret = cmd.ExecuteNonQuery() > 0 ? 1 : 0;
                            
                            return ret ;
                        }
                    }
                    else
                    {
                        if (sqlParms != null)
                            cmd.Parameters.AddRange(sqlParms);

                        int ret = cmd.ExecuteNonQuery() > 0 ? 1 : 0;
                        conn.Close();
                        return ret;
                    }
                
                }
              
                
            }
            catch(Exception ex) 
            {
                Error = ex.Message;
                LogHelper.Err("RTSDK.RTModeDB.Helper.MySqlHelper.cs", "ExecuteHashtable()->" + sql, ex);
            }
            finally
            {
                if (_dbInfo.IsDebugSql)
                    Console.WriteLine(sql);
            }

            return -1000;
        }

        public Hashtable[] DataReaderToHashtable(MySqlDataReader dr)
        {
            List<Hashtable> lt = new List<Hashtable>();
            lock (dr)
            {
                while (dr.Read())
                {
                    Hashtable ht = new Hashtable();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        try
                        {
                            if (!ht.ContainsKey(dr.GetName(i)))
                            {
                                //ht.Add(dr.GetName(i), System.Web.HttpUtility.HtmlDecode(dr[i].ToString()));
                                if (dr[i].GetType()==typeof(MySqlDateTime))
                                {
                                    ht.Add(dr.GetName(i), dr[i].ToString());
                                }
                                else if (dr[i].GetType() == typeof(DBNull))
                                {
                                    ht.Add(dr.GetName(i), "");
                                }
                                else
                                    ht.Add(dr.GetName(i), dr[i]);
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex);
                        }
                    }
                    lt.Add(ht);
                }
                Error = "";
            }
            return lt.ToArray();
        }


        public int Insert(string table, Hashtable[] hts)
        {
            _lastSql = "";
            Error = "";

            try
            {
                if (hts == null || hts.Length == 0)
                    return 0;


                int insertCount = 0;
                string tmpPath = BaseHelper.Base.SysHelper.AppPath + "\\mysql_insert_" + BaseHelper.Encrypt.GuidHelper.NewID() + ".dat";
                StringBuilder sb = new StringBuilder();

                int colCount=hts[0].Keys.Count;
                string line = "";
                List<string> ltCols = new List<string>();
                foreach (string key in hts[0].Keys)
                    ltCols.Add(key);

                foreach (Hashtable ht in hts)
                {
                    line = "";
                    foreach (string key in ht.Keys)
                        line += "\""+ ht[key].ToString().Replace("\"","\"\"") + "\",";

                    sb.Append(line.Substring(0, line.Length - 1) + "\r\n");
                }

                try
                {
                    File.WriteAllText(tmpPath, sb.ToString());


                    MySqlConnection conn = GetSqlConnection();
                    MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                                                {
                                                    FieldTerminator = ",",
                                                    FieldQuotationCharacter = '"',
                                                    EscapeCharacter = '"',
                                                    LineTerminator = "\r\n",
                                                    FileName = tmpPath,
                                                    NumberOfLinesToSkip = 0,
                                                    TableName = table,
                                                };

                    bulk.Columns.AddRange(ltCols.ToArray());//根据标题列对应插入

                    if (_dbInfo.KeepLive)
                    {
                        lock (_locker)
                        {
                            insertCount = bulk.Load();
                        }
                    }
                    else
                    {
                        insertCount = bulk.Load();
                        conn.Close();
                    }

                }
                catch (Exception ex)
                {
                    Error = ex.Message;
                    LogHelper.Err("RT.Helper.Data.MsSqlHelper.cs", "ExecuteHashtable()->Insert.MySqlBulkLoader()", ex);
                }
                finally
                {
                    if (File.Exists(tmpPath))
                        File.Delete(tmpPath);
                }

                return insertCount;

            }
            catch (Exception ex)
            {
                Error = ex.Message;
                LogHelper.Err("RT.Helper.Data.MsSqlHelper.cs", "ExecuteHashtable()->Insert.MySqlBulkLoader()", ex);
            }
            return -1000;
        }

        public int Insert(string table, Hashtable ht, bool HasBinaryField = false)
        {
            string keys = "";
            string values = "";
            MySqlParameter[] sqlParms = null;

            if (ht != null)
            {
                if (HasBinaryField)
                {
                    List<MySqlParameter> lt = new List<MySqlParameter>();
                    MySqlParameter param;

                    foreach (string key in ht.Keys)
                    {
                        keys += "`" + key + "`,";
                        values += "@" + key + ",";

                        if (ht[key] is string)
                            param = new MySqlParameter("@" + key, System.Web.HttpUtility.HtmlEncode(ht[key].ToString()));
                        else
                            param = new MySqlParameter("@" + key, ht[key]);

                        lt.Add(param);
                    }
                    sqlParms = lt.ToArray();
                }
                else
                {
                    foreach (string key in ht.Keys)
                    {
                        keys += "`" + key + "`,";
                        if(ht[key]==null)
                            values += "'',";   //12.6 准备将这里屏蔽掉，因为select的时候byte【】 会出问题
                        else
                            values += "'" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";   //12.6 准备将这里屏蔽掉，因为select的时候byte【】 会出问题

                        //values += "'" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";
                        //values += "'" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";   //12.6 准备将这里屏蔽掉，因为select的时候byte【】 会出问题

                    }

                }
            }


            if (keys.Length > 0)
            {
                keys = keys.Substring(0, keys.Length - 1);
                values = values.Substring(0, values.Length - 1);
            }

            string sql = string.Format("insert into {0}({1}) values({2})", table, keys, values);

            return NonQuery(sql, sqlParms);
        }

        public Hashtable[] Select(string table, string where = "", string field = "*", string orderby = "",int page=-1,int pageSize=30)
        {
            string sql;

            if (page==-1)
            {
                sql = string.Format("select {0} from {1} {2} {3}", field, table, where.Length > 0 ? "where " + where : "", orderby.Length > 0 ? "order by " + orderby : "");
            }
            else
            {
                if(page>0)
                    page--;
                sql = string.Format("select {0} from {1} {2} {3} limit {4},{5}", field, table, where.Length > 0 ? "where " + where : "", orderby.Length > 0 ? "order by " + orderby : "",pageSize*page,pageSize);
            }            

            return ExecuteHashtable(sql);
        }

        public int NonQuery(string table, string where, string field = "*")
        {
            string sql = string.Format("select {0} from {1} {2}", field, table, where.Length > 0 ? "where " + where : "");
            return NonQuery(sql);
        }

        public bool IsSelectHasRow(string table, string where = "")
        {
            string sql = string.Format("select * from {0} {1}", table, where.Length>0?"where " + where:"");
            return IsDataReaderHasRow(sql);
        }


        public int Count(string tableName, string where)
        {
            Hashtable[] ht = Select(tableName, where, "count(*) as datarow");
            if (ht != null)
            {
                return Convert.ToInt32(ht[0]["datarow"]);
            }

            return -1;
        }

        public int Update(string table,Hashtable ht,string where, bool HasBinaryField = false,bool IsValue=false)
        {

            string values = "";
            MySqlParameter[] sqlParms = null;

            if (HasBinaryField)
            {
                List<MySqlParameter> lt = new List<MySqlParameter>();
                MySqlParameter param;

                foreach (string key in ht.Keys)
                {
                    values += "`" + key + "`=@" + key + ",";

                    if (ht[key] is string)
                        param = new MySqlParameter("@" + key, System.Web.HttpUtility.HtmlEncode(ht[key].ToString()));
                    else
                        param = new MySqlParameter("@" + key, ht[key]);

                    lt.Add(param);
                }
                sqlParms = lt.ToArray();
            }
            else
            {

                foreach (string key in ht.Keys)
                {
                    if (ht[key] == null)
                    {
                        values += "`" + key + "`='',";
                    }
                    else
                    {
                        if(IsValue)
                            values += "`" + key + "` "  + ht[key].ToString() +  ",";
                        else
                            values += "`" + key + "`='" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";
                    }
                }
            }

            if (values.Length > 0)
            {
                values = values.Substring(0, values.Length - 1);
            }
            
            string sql = string.Format("update {0} set {1} where {2}",  table,values,where);
            return NonQuery(sql,sqlParms);
        }
        
        public int Delete(string table, string where="")
        {
            string sql = string.Format("delete from {0} {1}", table, where.Length > 0 ? "where " + where : "");
            return NonQuery(sql);
        }


        public string[] GetTables()
        {
            List<string> lt = new List<string>();

            this.NonQuery("use " + _dbInfo.DBName);

            Hashtable[] hts = this.ExecuteHashtable( "show tables");
            if (hts != null)
            {

                foreach (Hashtable ht in hts)
                {
                    foreach(string value in ht.Values)
                        lt.Add(value);
                }
            }

            return lt.ToArray();
        }

        public string[] GetFields(string table)
        {
            List<string> lt = new List<string>();

            Hashtable[] hts = this.ExecuteHashtable("select COLUMN_NAME from information_schema.COLUMNS where table_name='" + table + "'");
            if (hts != null)
            {
                foreach (Hashtable ht in hts)
                {
                    lt.Add(ht["COLUMN_NAME"].ToString());
                }
            }

            return lt.ToArray();
        }

        public int Truncate(string tableName)
        {
            string sql = "truncate table " + tableName;
            return NonQuery(sql);
        }




        public void Close()
        {
            this.Dispose();
        }

        public void Dispose()
        {
            try
            {
                if (_conn != null)
                    _conn.Close();

                _conn = null;
                _dbInfo = null;

                GC.SuppressFinalize(this);
            }
            catch { }
        }
    }
}
